<?php
require_once "Geocoder.class.php";
$db = new PDO("mysql:dbname=hair;host=localhost", "hairdresser", "hair");
if(!$db instanceof PDO) throw new Exception("Cannot connect to database");
$stmt = $db->prepare("select id, address, state from hairdressing where longitude is null or latitude is null");
if(!$stmt instanceof PDOStatement) throw new Exception("Cannot prepare query");
if(!$stmt->execute()) throw new Exception("Cannot execute query: " . print_r($stmt->errorInfo(), true));
$geocoder = new Geocoder("ABQIAAAAdBRyHO_KLbNV2CTUZIm51xSRg3LV6j1BwV-MCtqK8gvlcguFYhSz2l6Jv7opxnIaPLb-KmkwT3SuiA");
/*
while($row = $stmt->fetch()) {
	$queryAddress = $row['address'] . ' ' . $row['state'] . ', Spain';
	echo "Reading address " . $queryAddress . "\n";
	$xmlString = $geocoder->performRequest($queryAddress, 'xml');
	$xml = new SimpleXMLElement($xmlString);
	$point = $xml->Response->Placemark->Point->coordinates;
	list($longitude, $latitude, $elevation) = explode(',', $point, 3);
	echo "longitude=$longitude latitude=$latitude\n";
	$update = $db->prepare("update hairdressing set longitude=:longitude, latitude=:latitude where id=:id");
    $params = array(":longitude" => $longitude, ":latitude" => $latitude, ":id" => $row['id']);
    print_r($params);
	if(!$update->execute($params)) {
        throw new Exception("Cannot execute update: " . print_r($update->errorInfo()));
    }
    echo "Updated rows: {$update->rowCount()}\n";
}
*/

$stmt = $db->prepare("select id, name from city where longitude is null or latitude is null");
if(!$stmt instanceof PDOStatement) throw new Exception("Cannot prepare query");
if(!$stmt->execute()) throw new Exception("Cannot execute query: " . print_r($stmt->errorInfo(), true));
while($row = $stmt->fetch()) {
	$queryAddress = $row['name'] . ", spain";
	echo "Reading address " . $queryAddress . "\n";
	$xmlString = $geocoder->performRequest($queryAddress, 'xml');
	$xml = new SimpleXMLElement($xmlString);
	$point = $xml->Response->Placemark->Point->coordinates;
	list($longitude, $latitude, $elevation) = explode(',', $point, 3);
	echo "longitude=$longitude latitude=$latitude\n";
	$update = $db->prepare("update city set longitude=:longitude, latitude=:latitude where id=:id");
    $params = array(":longitude" => $longitude, ":latitude" => $latitude, ":id" => $row['id']);
    print_r($params);
	if(!$update->execute($params)) {
        throw new Exception("Cannot execute update: " . print_r($update->errorInfo()));
    }
    echo "Updated rows: {$update->rowCount()}\n";
}
